package com.hoyo.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.hoyo.domain.Channel;

@Mapper
public interface ChannelMapper {
	
	@Select("select * from channel")
	List<Channel> findAll()throws Exception;
	
	@Select("select c.*, mv.* from channel c "
			+ "left join (select uc.channel_id, count(*) movieCount "
			+ "from movie mv left join user_catalog uc "
			+ "on uc.id=mv.catalog_id where uc.is_showed=true "
			+ "group by channel_id) mv "
			+ "on mv.channel_id = c.id")
	List<Channel> findAllChannelsAndCountMovieInEveryChannel()throws Exception;
	

	@Select("select c.*, mv.*, (select count(*) from channel) total "
			+ "from channel c left join (select uc.channel_id, count(*) movieCount "
			+ "from movie mv left join user_catalog uc on uc.id=mv.catalog_id "
			+ "group by channel_id) mv on mv.channel_id = c.id "
			+ "limit #{0},#{1}")
	List<Channel> findAllChannelsAndCountMovieInEveryChannelLimitBy(int offset, int count)throws Exception;
	
	@Insert("insert into channel(channel_name, description) values(#{channelName},#{description})")
	void save(Channel channel);
	
	@Update("update channel set channel_name=#{1}, description=#{2}  where id=#{0}")
	void update(int id, String channelName, String description);
	
	@Update("delete from channel where id=#{0}")
	void remove(int id);
}
